Create table MessageRemovalReasons
(
	MessageRemovalReasonId int identity(1, 1) primary key,
	Title nvarchar(200) not null,
	ReasonText nvarchar(200),
	HideMessage bit not null default(0)
)
go
Alter Table Messages Add MessageRemovalReasonId int foreign key references MessageRemovalReasons(MessageRemovalReasonId)
go

Create procedure SPMessageRemovalReasonsGetAll
As
Begin

	Select MessageRemovalReasonId, Title, ReasonText, HideMessage
	From MessageRemovalReasons

End
Go

Insert into MessageRemovalReasons (Title, ReasonText, HideMessage) 
	values ('Inappropriate content', 'The message was removed by a forum moderator (Inappropriate)', 0)
	
Insert into MessageRemovalReasons (Title, ReasonText, HideMessage) 
	values ('Off topic', 'The message was removed by a forum moderator (Off topic)', 0)
	
Insert into MessageRemovalReasons (Title, ReasonText, HideMessage) 
	values ('Offensive content', 'The message was removed by a forum moderator (Offensive)', 0)
	
Insert into MessageRemovalReasons (Title, ReasonText, HideMessage) 
	values ('Spam - Hide from Users', null, 1)
go

Alter view MessagesComplete
As

Select  
	M.TopicId  
	,M.MessageId  
	,M.MessageBody  
	,M.MessageCreationDate  
	,M.MessageLastEditDate  
	,M.ParentId  
	,M.UserId  
	,M.Active  
	,U.UserName  
	,U.UserSignature  
	,U.UserGroupId  
	,G.UserGroupName  
	,U.UserPhoto  
	,U.UserRegistrationDate
	,M.MessageRemovalReasonID
From  
dbo.Messages M  
	INNER JOIN dbo.Users U ON U.UserId = M.UserId  
	INNER JOIN dbo.UsersGroups G ON G.UserGroupId = U.UserGroupId  
	LEFT JOIN dbo.Messages P ON P.TopicId = M.TopicId AND P.MessageId = M.ParentId AND P.Active = 1
	
go

Alter procedure SPMessagesGetByTopicFrom
	@TopicId int=1,
	@FirstMsg int=13,
	@Amount int=10
As
Begin

	Select 
		RowNumber
		,TopicId  
		,MessageId  
		,MessageBody  
		,MessageCreationDate  
		,MessageLastEditDate  
		,ParentId  
		,UserId  
		,UserName  
		,UserSignature  
		,UserGroupId  
		,UserGroupName  
		,UserPhoto  
		,UserRegistrationDate  
		,Active
		,ReasonText
		,HideMessage 
	From (  
		Select   
			ROW_NUMBER() OVER (ORDER BY M.TopicId, M.MessageId) AS RowNumber
			,M.TopicId  
			,M.MessageId  
			,M.MessageBody  
			,M.MessageCreationDate  
			,M.MessageLastEditDate  
			,M.ParentId  
			,UserId  
			,UserName  
			,UserSignature  
			,UserGroupId  
			,UserGroupName  
			,UserPhoto  
			,UserRegistrationDate  
			,M.Active
			,MessageRemovalReasons.ReasonText
			,MessageRemovalReasons.HideMessage
		From    
			dbo.MessagesComplete M
				left outer join MessageRemovalReasons on MessageRemovalReasons.MessageRemovalReasonID =  M.MessageRemovalReasonID 
		Where   
			M.TopicId = @TopicId  
			AND M.MessageId > @FirstMsg
	) M  
	Where  
	RowNumber <= @Amount

End
Go

Alter procedure SPMessagesDelete
	@TopicId int
	,@MessageId int
	,@UserId int
	,@MessageRemovalReasonID int = null
As
Begin

	Update Messages  
	Set  
		Active = 0  
		,MessageLastEditDate = GETUTCDATE()  
		,MessageLastEditUser = @UserId
		,MessageRemovalReasonID = @MessageRemovalReasonID
	WHERE  
		TopicId = @TopicId  
		AND MessageId = @MessageId

End
go